﻿-- DROP SHADOW/AUDIT
CREATE PROCEDURE [todoc].[sspDropShadowTable]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF A SHADOW/AUDIT TABLE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @ShadowTableNamePrefix NVARCHAR(20) = '';
DECLARE @ShadowTableNamePostfix NVARCHAR(20) = '';
DECLARE @MaintenanceMode NVARCHAR(5) = 'False';

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@ShadowTableNamePrefix = MAX((CASE WHEN ep.[name] = 'Todoc: Table name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@ShadowTableNamePostfix = MAX((CASE WHEN ep.[name] = 'Todoc: Table name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@MaintenanceMode = MAX((CASE WHEN ep.[name] = 'Todoc: Maintenance mode' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateShadowTable')
		AND ep.[name] IN('Todoc: Table name postfix', 'Todoc: Maintenance mode');

	IF (@MaintenanceMode = 'True')
		RETURN 0;

	-- create the drop
	SET @SQL = 
	'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) + ''') 
	AND TYPE IN (N''U'')) DROP TABLE ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@ShadowTableNamePrefix + @TableName + @ShadowTableNamePostfix) + ';' + CHAR(13) + CHAR(13);

	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;